import pymysql


class MySQLHelper:
    def __init__(self, config: dict):
        self.host = config['host']
        self.port = config['port']
        self.user = config['user']
        self.password = config['password']
        self.database = config['database']
        self.connect()

    def connect(self):
        self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, database=self.database,connect_timeout=10)

    def execute(self, sql: str, params: tuple = None):
        """执行 SQL 语句，无返回值"""
        id = 0
        try:
            self.is_connect()
            cursor = self.conn.cursor()
            cursor.execute(sql, params or ())
            id = cursor.lastrowid
            self.conn.commit()
            cursor.close()
            self.close()
        except Exception as e:
            print(f"SQL 错误:{sql}, {e}")
            return id
        return id

    def is_connect(self):
        """检查链接是否正常"""
        try:
            self.conn.ping(reconnect=True)
        except:
            self.connect()

    def query_get_value(self, sql: str, params: tuple = None):
        """查询并返回单个值"""
        self.is_connect()
        cursor = self.conn.cursor()
        cursor.execute(sql, params or ())
        result = cursor.fetchone()
        cursor.close()
        self.close()
        return result[0] if result[0] else None

    def query_one(self, sql: str, params: tuple = None):
        """查询并返回单条数据"""
        self.is_connect()
        cursor = self.conn.cursor()
        cursor.execute(sql, params or ())

        result = cursor.fetchone()
        desc = cursor.description
        row = {}
        if result:
            for i in range(0, len(result)):
                row[desc[i][0]] = result[i]

        cursor.close()
        self.close()
        return row

    def query_all_list(self, sql: str, params: tuple = None):
        """查询并返回所有数据"""
        self.is_connect()
        cursor = self.conn.cursor()
        cursor.execute(sql, params or ())
        result = cursor.fetchall()
            
        cursor.close()
        self.close()
        return result
    
    def query_all(self, sql: str, params: tuple = None):
        """查询并返回所有数据"""
        self.is_connect()
        cursor = self.conn.cursor()
        cursor.execute(sql, params or ())
        result = cursor.fetchall()
        rows = []
        desc = cursor.description
        for cloum in result:
            row = {}
            for i in range(0, len(cloum)):
                row[desc[i][0]] = cloum[i]
            rows.append(row)
        cursor.close()
        self.close()
        return rows

    def insert(self, table, data: dict):
        """插入一条数据"""
        try:
            self.is_connect()
            fields = ','.join(data.keys())
            values = ','.join(['%s'] * len(data))
            sql = 'INSERT INTO %s (%s) VALUES (%s)' % (table, fields, values)
            # print(f"insert sql: {sql}")
            # print(tuple(data.values()))
            id = self.execute(sql, tuple(data.values()))
            return id
        except Exception as e:
            print("insert 操作错误：",e)
            return False

    def update(self, table, data: dict, where: str = '1=2', where_params: tuple = None):
        """更新数据"""
        try:
            self.is_connect()
            set_values = ','.join(['%s=%s' % (field, '%s') for field in data.keys()])
            sql = 'UPDATE %s SET %s WHERE %s' % (table, set_values, where)
            params = tuple(data.values())
            if where_params:
                params += where_params
            self.execute(sql, params)
            return True
        except:
            return False

    def delete(self, table, where: str = '1=2', where_params: tuple = None):
        """删除数据"""
        self.is_connect()
        sql = 'DELETE FROM %s WHERE %s' % (table, where)
        try:
            if where_params:
                self.execute(sql, where_params)
            else:
                self.execute(sql)
        except Exception as e:
            print("delete 操作失败：", str(e))

    def close(self):
        """关闭数据库连接"""
        self.conn.close()